 package com.example.haiti.infrastructure.surveyor;


import java.lang.reflect.Array;
import java.util.HashMap;
import java.util.Map;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteQueryBuilder;
import android.database.sqlite.SQLiteStatement;
import android.util.Log;

public class SqlHelper extends SQLiteOpenHelper {
    private static final String DATABASE_PATH = "/data/data/com.example.haiti.infrastructure.surveyor/databases/";

    public static final String DATABASE_NAME = "Haiti_Infrastructure";

    public static final String TABLE_NAME = "Structures";
    public static final int StructureItems_VERSION = 1;

    public static final String COLUMN_ID = "_id";               // 0
    public static final String COLUMN_STRUCTURE_NAME = "structureName";          // 1
    public static final String COLUMN_STRUCTURE_DESC = "structureDesc";// 2
    public static final String COLUMN_STRUCTURE_STATUS= "structureStatus";// 3
    public static final String COLUMN_STRUCTURE_PREVIEW_PICTURE= "structurePictureThumbnail";// 4
    public static final String COLUMN_STRUCTURE_ADDRESS= "structureAddress";// 5
    public static final String COLUMN_SURVEYOR_NAME= "surveyorName";// 6
    public static final String COLUMN_SURVEYOR_PHONE_NUMBER= "phoneNumber";// 7
    
    public static final String COLUMN_STRUCTURE_OWNED= "structureOwned";// 8
    public static final String COLUMN_STRUCTURE_YEAR_BUILT= "yearBuilt";// 9
    public static final String COLUMN_STRUCTURE_BUILD_DURATION= "buildDuration";// 10
    public static final String COLUMN_STRUCTURE_BUILDER= "builder";// 11

    public static final String COLUMN_STRUCTURE_STEEL_WALLS= "steelWalls";// 12
    public static final String COLUMN_STRUCTURE_NUMBER_OF_FLOORS= "nFloors";// 13
    public static final String COLUMN_STRUCTURE_ROOF_TYPE= "roofType";// 14
    public static final String COLUMN_STRUCTURE_NUMBER_OF_CHILDREN= "nChildren";// 15
    public static final String COLUMN_STRUCTURE_NUMBER_OF_ADULTS= "nAdults"; //16
    
    public static final String COLUMN_STRUCTURE_PHOTO_FRONT= "photoFront";// 17
    public static final String COLUMN_STRUCTURE_NOTE_FRONT= "noteFront";// 18
    
    public static final String COLUMN_STRUCTURE_PHOTO_RIGHT= "photoRight";// 19
    public static final String COLUMN_STRUCTURE_NOTE_RIGHT= "noteRight";// 20
    
    public static final String COLUMN_STRUCTURE_PHOTO_BACK= "photoBack";// 21
    public static final String COLUMN_STRUCTURE_NOTE_BACK= "noteBack";// 22
    
    public static final String COLUMN_STRUCTURE_PHOTO_LEFT= "photoLeft";// 23
    public static final String COLUMN_STRUCTURE_NOTE_LEFT= "noteLeft";// 24
      
    public SQLiteDatabase dbSqlite;
    private Context mContext;

    public SqlHelper(Context context) {
        super(context, DATABASE_NAME, null, 1);
        mContext = context;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        createDB(db);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.w("SqlHelper", "Upgrading database from version " + oldVersion
                + " to " + newVersion + ", which will destroy all old data");

        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME + ";");

        createDB(db);
    }

    public void createDatabase(SQLiteDatabase db) {
        createDB(db);
    }

    private void createDB(SQLiteDatabase db) {
        if (db == null) {
            db = mContext.openOrCreateDatabase(DATABASE_NAME, 0, null);
        }
        
        // REQ:3.5.4.R2  use of a mysql database 
        db.execSQL("CREATE TABLE IF NOT EXISTS "
                + TABLE_NAME
                + " (_id INTEGER PRIMARY KEY, structureName VARCHAR(50),"
                + " structureDesc VARCHAR(100),"
                + " structureStatus VARCHAR(25), structurePictureThumbnail VARCHAR(100),"
                + " structureAddress VARCHAR(100),"
                + " surveyorName VARCHAR(25), phoneNumber VARCHAR(25)," 
                + " structureOwned VARCHAR(25), yearBuilt VARCHAR(4)," 
                + " buildDuration VARCHAR(25), builder VARCHAR(50)," 
                + " steelWalls VARCHAR(25), nFloors VARCHAR(50)," 
                + " roofType VARCHAR(25), nChildren VARCHAR(50)," 
                + " nAdults VARCHAR(25)," 
                + " photoFront VARCHAR(255), noteFront VARCHAR(160)," 
                + " photoRight VARCHAR(255), noteRight VARCHAR(160)," 
                + " photoBack VARCHAR(255), noteBack VARCHAR(160)," 
                + " photoLeft VARCHAR(255), noteLeft VARCHAR(160)" 
                + ");");
               
        db.setVersion(StructureItems_VERSION);
    }

    public void openDataBase() throws SQLException {
        String myPath = DATABASE_PATH + DATABASE_NAME;
        
        dbSqlite = SQLiteDatabase.openDatabase(myPath, null,
                SQLiteDatabase.OPEN_READWRITE);
    }

    @Override
    public synchronized void close() {
        if (dbSqlite != null)
            dbSqlite.close();
        super.close();
    }

    public Cursor getCursor(String selection) {
        SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder();

        queryBuilder.setTables(TABLE_NAME);

        String[] asColumnsToReturn = new String[] { COLUMN_ID, COLUMN_STRUCTURE_NAME,
                COLUMN_STRUCTURE_DESC, COLUMN_STRUCTURE_STATUS, COLUMN_STRUCTURE_PREVIEW_PICTURE, 
                COLUMN_STRUCTURE_ADDRESS, COLUMN_SURVEYOR_NAME, COLUMN_SURVEYOR_PHONE_NUMBER, COLUMN_STRUCTURE_OWNED,
                COLUMN_STRUCTURE_YEAR_BUILT, COLUMN_STRUCTURE_BUILD_DURATION, COLUMN_STRUCTURE_BUILDER,
                COLUMN_STRUCTURE_STEEL_WALLS, COLUMN_STRUCTURE_NUMBER_OF_FLOORS, COLUMN_STRUCTURE_ROOF_TYPE,
                COLUMN_STRUCTURE_NUMBER_OF_CHILDREN, COLUMN_STRUCTURE_NUMBER_OF_ADULTS, COLUMN_STRUCTURE_PHOTO_FRONT,
                COLUMN_STRUCTURE_NOTE_FRONT, COLUMN_STRUCTURE_PHOTO_RIGHT, COLUMN_STRUCTURE_NOTE_RIGHT,
                COLUMN_STRUCTURE_PHOTO_BACK, COLUMN_STRUCTURE_NOTE_BACK, COLUMN_STRUCTURE_PHOTO_LEFT, 
                COLUMN_STRUCTURE_NOTE_LEFT};
        
        Cursor mCursor = queryBuilder.query(dbSqlite, asColumnsToReturn, selection,
                null, null, null, COLUMN_STRUCTURE_NAME+" ASC");

        return mCursor;
    } 
    
    public int getStructureID(Cursor currentCursor){
    	
    	int structureID = currentCursor.getInt(currentCursor.getColumnIndex(COLUMN_ID)); 
    	   	
    	return structureID;
    }
     
    public void delete_byID(int id){
    	dbSqlite.delete(TABLE_NAME, COLUMN_ID+"="+id, null);
    	}
    
    public void createStructureEntry(String structureName, String structureDesc) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.execSQL("INSERT INTO " + TABLE_NAME 
        	+ " VALUES(NULL,"			// Structure ID
	        +"'"+structureName+"',"		// Structure Name
	        +"'"+structureDesc+"',"		// Structure Description
	        +"'New'," 					// Structure Status
	        +"NULL," 					// Structure Thumbnail
	        +"NULL," 					// Structure Address
	        +"NULL," 					// Surveyor Name
	        +"NULL," 					// Surveyor Phone Number
	        +"NULL," 					// Structure Owned	        
	        +"NULL," 					// Year Built	        
	        +"NULL," 					// Build Duration	        
	        +"NULL," 					// Builder	        
	        +"NULL," 					// Steel Walls?	        
	        +"NULL," 					// Number of Floors	        
	        +"NULL," 					// Roof Type        
	        +"NULL," 					// # of Children	        
	        +"NULL," 					// # of Adults	        
	        +"NULL," 					// Photo Front	        
	        +"NULL," 					// note Front	        
	        +"NULL," 					// Photo Right	        
	        +"NULL," 					// note Right	        
	        +"NULL," 					// Photo Back	        
	        +"NULL," 					// Note Back	        
	        +"NULL," 					// Photo Left        
	        +"NULL" 					// Note Left	        	        
	        +");");   	   	
    }  
    
    public void updateStructureEntry(String[][] databaseUpdates, int structureID) {
        ContentValues values = new ContentValues();
        	
        for (int i=0 ; i<databaseUpdates.length; i++){
        	values.put(databaseUpdates[i][0], databaseUpdates[i][1]);		
        }
                           
       dbSqlite.update(SqlHelper.TABLE_NAME, values, COLUMN_ID  + "=" + structureID, null);
    }

}